from keras.layers import Dense
from keras.layers import LSTM
import pandas as pd
import os
from keras.models import Sequential
from sklearn.preprocessing import MinMaxScaler
import numpy as np
from sklearn import metrics
import sys
import pymysql
import time

def create_dataset(dataset, label, look_back):
    dataX, dataY = [], []
    for i in range(len(dataset) - look_back):
        a = dataset[i:(i + look_back)]
        dataX.append(a)
        dataY.append(label[i + look_back])
    return np.array(dataX), np.array(dataY)

def inverse_transform_col(scaler, y, n_col):
    y = y.copy()
    y -= scaler.min_[n_col]
    y /= scaler.scale_[n_col]
    return y

def PuDong(feature,Y, distinct):

    # 参数1:主机名或工P地址; 参数2:用户名; 参数3:密码; 参数4: 数据库名称
    db = pymysql.connect(host="localhost:3307", user="app_user",password="123456", database="smart_cim")
    sql = 'select * from ' + distinct


    dataframes = pd.read_sql(sql, db)
    dataframe = dataframes[feature]
    dataY = dataframes[Y]

    # 输入特征
    channel_in = len(feature)

    datasets = dataframe.values
    dataY =dataY.values
    dataset = datasets.astype('float32')

    scaler = MinMaxScaler(feature_range=(0, 1))
    dataset = scaler.fit_transform(dataset)
    # print('=======',dataset)
    scalerY = MinMaxScaler(feature_range=(0, 1))
    dataY =scalerY.fit_transform(dataY.reshape(-1,1))

    train_size = int(len(dataset) * 0.8)
    # print(train_size)
    trainlist = dataset[:train_size]
    testlist = dataset[train_size:]
    trainLabel = dataY[:train_size]
    testLabel = dataY[train_size:]

    look_back = 1
    trainX, trainY = create_dataset(trainlist, trainLabel, look_back)
    testX, testY = create_dataset(testlist, testLabel, look_back)
    model = Sequential()
    model.add(LSTM(20, input_shape=(1, channel_in)))
    model.add(Dense(1))
    model.compile(loss='mean_squared_error', optimizer='adam')
    # verbose：日志显示，0为不在标准输出流输出日志信息，1为输出进度条记录，2为每个epoch输出一行记录
    model.fit(trainX, trainY, epochs=300, batch_size=2, verbose=0)
  
    trainPredict = model.predict(trainX)
    train_r2 = metrics.r2_score(trainY,trainPredict)
    train_mse = metrics.mean_squared_error(trainY,trainPredict)
    # print(train_r2,train_mse)
    testPredict = model.predict(testX)
    test_r2 = metrics.r2_score(testY, testPredict)
    test_mse = metrics.mean_squared_error(testY, testPredict)


    trainPredict = inverse_transform_col(scalerY, trainPredict, 0)
    trainY = inverse_transform_col(scalerY, trainY, 0)
    testPredict = inverse_transform_col(scalerY, testPredict, 0)
    testY = inverse_transform_col(scalerY, testY, 0)



    testX1 = []
    testX1.append(datasets[len(datasets)-1])
    testX1 = np.array(testX1)
    testX1_T = scaler.transform(testX1)
    testX1_T = testX1_T.reshape(testX1.shape[0], 1, testX1.shape[1])
    test_pre = model.predict(testX1_T)

    testPre = inverse_transform_col(scalerY, test_pre, 0)

    # 关闭数据库连接
    db.close()
    return testPre[0][0],distinct

if __name__ == '__main__':
    os.environ['TF_CPP_MIN_LOG_LEVEL'] = '0'

    # 存放所有的参数 最后一个为第一个列表的长度（分割时用）
    list_str = []
    # list_str = ['企业数', '消费数', '资本量', '雇员数','人口数','人口数','data_pudong']
    # list_str = ['company', 'consume', 'capital', 'house_price', 'employees', 'population', 'population']

    print(str(sys.argv[1:]))
    for i in range(1, len(sys.argv)):
        list_str.append(sys.argv[i].replace(",", ""))
    # 处理第一个还有最后一个元素的格式
    list_str[0] = list_str[0].replace("[", "")
    list_end = list_str[len(sys.argv) - 2].split(']')
    list_str[len(sys.argv) - 2] = list_end[0]
    list_str.append(list_end[1])
    print(list_str)
    n = len(list_str)

    # 将中文转化为英文（数据库中字段为英文）
    for i in range(n):
        if list_str[i] == '企业数':
            list_str[i] = 'company'
        if list_str[i] == '消费数':
            list_str[i] = 'consume'
        if list_str[i] == '资本量':
            list_str[i] = 'capital'
        if list_str[i] == '区域市价':
            list_str[i] = 'house_price'
        if list_str[i] == '雇员数':
            list_str[i] = 'employees'
        if list_str[i] == '人口数':
            list_str[i] = 'population'

    print(list_str)
    start = time.time()
    result,distinct = PuDong(list_str[0:n-2],list_str[n-2], list_str[-1])
    end = time.time()
    print("process time：", end-start)
    print(distinct+','+str(result)+','+list_str[n-2])

